package entity

import (
	"fmt"
	"strconv"

	"gorm.io/gorm"
	"shqsoft.cn/pas3ui/base"
)

//经济资本
type KhjlDjQt struct {
	ID     int     `gorm:"AUTO_INCREMENT;primary_key;column:ID"`
	DTID   int     `gorm:"column:DTID"`
	NIAN   int     `gorm:"column:NIAN"`
	JIDU   int     `gorm:"column:JIDU"`
	EMID   int     `gorm:"column:EMID"`
	EMNO   string  `gorm:"column:EMNO"`
	EMNM   string  `gorm:"column:EMNM"`
	GJJ    float64 `gorm:"column:GJJ"`
	FQYW   float64 `gorm:"column:FQYW"`
	XYK    float64 `gorm:"column:XYK"`
	VIP    float64 `gorm:"column:VIP"`
	SHKH   float64 `gorm:"column:SHKH"`
	DFGZ   float64 `gorm:"column:DFGZ"`
	SJYH   float64 `gorm:"column:SJYH"`
	BXXS   float64 `gorm:"column:BXXS"`
	JJXS   float64 `gorm:"column:JJXS"`
	SFDX   float64 `gorm:"column:SFDX"`
	GJS    float64 `gorm:"column:GJS"`
	CC     float64 `gorm:"column:CC"`
	ETC    float64 `gorm:"column:ETC"`
	GJJZH  float64 `gorm:"column:GJJZH"`
	XSZS   float64 `gorm:"column:XSZS"`
	XYKFQ  float64 `gorm:"column:XYKFQ"`
	XYKSS  float64 `gorm:"column:XYKSS"`
	DFJE   float64 `gorm:"column:DFJE"`
	SJYHXZ float64 `gorm:"column:SJYHXZ"`
}

//TableName 经济资本
func (KhjlDjQt) TableName() string {
	return "T_BASE_KHJL_DJ_QT"
}

func (t *KhjlDjQt) BeforeCreate(tx *gorm.DB) (err error) {
	err = tx.Raw(`SELECT "ID" FROM "T_SysUser" WHERE "SysUserID"=?`, t.EMNO).Row().Scan(&t.EMID)
	if err != nil {
		err = fmt.Errorf("员工%s在系统中不存在！", t.EMNM)
		return
	}
	return
}

func KhjlDjQtEF() base.EntityFunc {
	return func() interface{} {
		entity := KhjlDjQt{}
		return &entity
	}
}
func KhjlDjQtESF() base.EntitiesFunc {
	return func() interface{} {
		var entities []KhjlDjQt
		return &entities
	}
}

//ShIndiDjzbdrAfterImport 导入后执行分户处理
func KhjlDjQtAfterImport(tx *gorm.DB, params map[string]string) (err error) {
	nianStr := params["NIAN"]
	jidu, _ := strconv.Atoi(params["JIDU"])
	var dateStr string
	if jidu == 1 {
		dateStr = nianStr + "-03-31"
	} else if jidu == 2 {
		dateStr = nianStr + "-06-30"
	} else if jidu == 3 {
		dateStr = nianStr + "-09-30"
	} else {
		dateStr = nianStr + "-12-31"
	}
	err = base.CHDB.Exec("ALTER TABLE SH_KHJL_DJ_BB DELETE WHERE NIAN=? AND JIDU=?", nianStr, jidu).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("optimize table SH_KHJL_DJ_BB").Error
	if err != nil {
		return
	}
	sqlStr := `INSERT INTO SH_KHJL_DJ_BB (EMID,NIAN,JIDU,ZBID,WCZ,DF)
	WITH toDate(?) AS CURRDATE,addDays(toStartOfQuarter(addQuarters(CURRDATE,1)),-1) AS EDATE2,
	toStartOfYear(EDATE2) AS SDATE2,toYear(EDATE2) AS NF,toQuarter(EDATE2) AS JD,
	toYYYYMM(SDATE2) AS SMONT,toYYYYMM(EDATE2) AS EMONT,addDays(toStartOfYear(EDATE2),-1) AS QEDATE,toStartOfYear(QEDATE) AS QSDATE,
	dateDiff('day',SDATE2,EDATE2)+1 AS DAYS,dateDiff('day',QSDATE,QEDATE)+1 AS QDAYS,toYYYYMM(QSDATE) AS QSMONT,toYYYYMM(QEDATE) AS QEMONT
	SELECT EMID,NF,JD,31,IF(SUM(A.DCXRJ-B.QCXRJ)>=0,SUM(B.QCXRJ),SUM(A.DCXRJ)) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(31))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(31))) AS DF
	FROM
	(SELECT EMID,sumMerge(ACBL_CX)/DAYS DCXRJ
	FROM MFKB_EMPL_INDEX WHERE MONT>=SMONT AND MONT<=EMONT
	GROUP BY EMID) A FULL JOIN
	(SELECT EMID,sumMerge(ACBL_CX)/QDAYS QCXRJ
	FROM MFKB_EMPL_INDEX WHERE MONT>=QSMONT AND MONT<=QEMONT
	GROUP BY EMID) B ON A.EMID=B.EMID
	GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,32,IF(SUM(A.DCXRJ-B.QCXRJ)>0,SUM(A.DCXRJ-B.QCXRJ),0) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(32))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(32))) AS DF
	FROM
	(SELECT EMID,sumMerge(ACBL_CX)/DAYS DCXRJ
	FROM MFKB_EMPL_INDEX WHERE MONT>=SMONT AND MONT<=EMONT
	GROUP BY EMID) A FULL JOIN
	(SELECT EMID,sumMerge(ACBL_CX)/QDAYS QCXRJ
	FROM MFKB_EMPL_INDEX WHERE MONT>=QSMONT AND MONT<=QEMONT
	GROUP BY EMID) B ON A.EMID=B.EMID
	GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,34,SUM(SALE_LS_FBBLC) WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(34))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(34))) AS DF
	FROM DFKB_EMPL_INDEX WHERE DTID>=SDATE2 AND DTID<=EDATE2
	GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,35,sum(NUM_LS_BXKH) WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(35))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(35))) AS DF
	FROM DFKB_INDI_CLIE_INDEX WHERE DTID=EDATE2
	GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,23,WCZ,DF
	FROM (SELECT EMID,KDID,
	SUM(ACBL1) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(23))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(23))) AS DF,
	dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
	FROM pas_olap_nj.DF_CM_RETAIL_LOAN_DUBIL
	WHERE DTID = EDATE2 AND KDNO IN(1,4,5) AND PERD>12
	GROUP BY EMID,KDID) T
	UNION ALL
	SELECT EMID,NF,JD,24,WCZ,DF
	FROM (SELECT EMID,KDID,
	SUM(ACBL1) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(24))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(24))) AS DF,
	dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
	FROM pas_olap_nj.DF_CM_RETAIL_LOAN_DUBIL
	WHERE DTID = EDATE2 AND KDNO=2 AND PERD>12
	GROUP BY EMID,KDID) T
	UNION ALL
	SELECT EMID,NF,JD,25,WCZ,DF
	FROM (SELECT EMID,KDID,
	SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(25))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(25))) AS DF,
	dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
	FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
	(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
	WHERE SDATE<=EDATE2 AND EDATE>=EDATE2) B ON A.ACID = B.ACID
	WHERE DISTR_DT>=SDATE2 AND DISTR_DT<=EDATE2 AND KDNO IN(1,5) AND LPR_INT_RATE_FLOAT_ANNUAL<35
	GROUP BY EMID,KDID) T
	UNION ALL
	SELECT EMID,NF,JD,26,WCZ,DF
	FROM (SELECT EMID,KDID,
	SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(26))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(26))) AS DF,
	dictGetUInt8('pas_olap_nj.DICT_INDI_LAKD','type',toUInt64(KDID)) AS KDNO
	FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
	(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
	WHERE SDATE<=EDATE2 AND EDATE>=EDATE2) B ON A.ACID = B.ACID
	WHERE DISTR_DT>=SDATE2 AND DISTR_DT<=EDATE2 AND KDNO IN(1,5)
	GROUP BY EMID,KDID) T
	UNION ALL
	SELECT EMID,NF,JD,28,
	SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(28))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(28))) AS DF
	FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
	(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
	WHERE SDATE<=EDATE2 AND EDATE>=EDATE2) B ON A.ACID = B.ACID
	WHERE DISTR_DT>=SDATE2 AND DISTR_DT<=EDATE2 AND LPR_INT_RATE_FLOAT_ANNUAL<30
	AND A.ACID IN (SELECT distinct toUInt64(ACID) FROM mysql.SH_INDI_PHJR)
	GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,29,
	SUM(toFloat64(DISTR_AMT)*DSVL) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(29))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(29))) AS DF
	FROM T_CM_RETAIL_LOAN_DUBIL_SUM_M A INNER JOIN
	(SELECT ACID,DIVI.EMID AS EMID, DIVI.DSVL AS DSVL FROM T_INDI_LOAN_DIVI_ZIPPER ARRAY JOIN DIVI
	WHERE SDATE<=EDATE2 AND EDATE>=EDATE2) B ON A.ACID = B.ACID
	WHERE DISTR_DT>=SDATE2 AND DISTR_DT<=EDATE2
	AND A.ACID IN (SELECT distinct toUInt64(ACID) FROM mysql.SH_INDI_PHJR)
	GROUP BY EMID
	UNION ALL
	select EMID,NF,JD,30,sum(ACBL1)/DAYS AS WCZ,if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(30))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(30))) AS DF
	from DF_CM_RETAIL_LOAN_DUBIL
	where DTID>=SDATE2 and DTID <=EDATE2 and JLID in (select JLID from T_CM_RETAIL_LOAN_DUBIL_SUM_M where AGT_NUM like 'N%' OR AGT_NUM like 'HP%')
	group by EMID
	UNION ALL
	SELECT EMID,NF,JD,40,IF(SUM(A.ZJL-B.QZJL)>0,SUM(A.ZJL-B.QZJL),0) AS WCZ,
	if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(40))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(40))) AS DF
	FROM
	(SELECT EMID,sum(ACBL_ZJL) ZJL
	FROM DFKB_EMPL_INDEX WHERE DTID=EDATE2
	GROUP BY EMID) A FULL JOIN
	(SELECT EMID,sum(ACBL_ZJL) QZJL
	FROM DFKB_EMPL_INDEX WHERE DTID=QEDATE
	GROUP BY EMID) B ON A.EMID=B.EMID
	GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,41,SUM(ZS_LS_4773) WCZ,
		if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(41))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(41))) AS DF
		FROM DFKB_EMPL_INDEX WHERE DTID>=SDATE2 AND DTID<=EDATE2
		GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,42,IF(SUM(A.KHS-B.KHS)>0,SUM(A.KHS-B.KHS),0) AS WCZ,
		if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(42))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(42))) AS DF
		FROM
		(SELECT EMID,sum(NUM_KHJB_YJX+NUM_KHJB_JK+NUM_KHJB_BJK) KHS
		FROM DFKB_EMPL_INDEX WHERE DTID=EDATE2
		GROUP BY EMID) A FULL JOIN
		(SELECT EMID,sum(NUM_KHJB_YJX+NUM_KHJB_JK+NUM_KHJB_BJK) KHS
		FROM DFKB_EMPL_INDEX WHERE DTID=QEDATE
		GROUP BY EMID) B ON A.EMID=B.EMID
		GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,43,IF(SUM(A.KHS-B.KHS)>0,SUM(A.KHS-B.KHS),0) AS WCZ,
		if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(43))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(43))) AS DF
		FROM
		(SELECT EMID,sum(NUM_KHJB_MSK) KHS
		FROM DFKB_EMPL_INDEX WHERE DTID=EDATE2
		GROUP BY EMID) A FULL JOIN
		(SELECT EMID,sum(NUM_KHJB_MSK) KHS
		FROM DFKB_EMPL_INDEX WHERE DTID=QEDATE
		GROUP BY EMID) B ON A.EMID=B.EMID
		GROUP BY EMID
	UNION ALL
	SELECT EMID,NF,JD,44,IF(SUM(A.KHS-B.KHS)>0,SUM(A.KHS-B.KHS),0) AS WCZ,
		if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(44))*(dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(44))) AS DF
		FROM
		(SELECT EMID,sum(NUM_KHJB_SH) KHS
		FROM DFKB_EMPL_INDEX WHERE DTID=EDATE2
		GROUP BY EMID) A FULL JOIN
		(SELECT EMID,sum(NUM_KHJB_SH) KHS
		FROM DFKB_EMPL_INDEX WHERE DTID=QEDATE
		GROUP BY EMID) B ON A.EMID=B.EMID
		GROUP BY EMID`
	err = base.CHDB.Exec(sqlStr, dateStr).Error
	if err != nil {
		return
	}
	sqlStr = `INSERT INTO SH_KHJL_DJ_BB (EMID,NIAN,JIDU,ZBID,WCZ,DF)
WITH toDate(?) AS EDATE,toYear(EDATE) AS NF,toQuarter(EDATE) AS JD
 SELECT EMID,NF,JD,27,toFloat64(GJJZH) AS WCZ,if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(27))*
 (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(27))) AS DF FROM mysql.T_BASE_KHJL_DJ_QT WHERE NIAN=NF AND JIDU=JD
 UNION ALL
 SELECT EMID,NF,JD,33,toFloat64(XSZS) AS WCZ,if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(33))*
 (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(33))) AS DF FROM mysql.T_BASE_KHJL_DJ_QT WHERE NIAN=NF AND JIDU=JD
 UNION ALL
 SELECT EMID,NF,JD,36,toFloat64(XYKFQ) AS WCZ,if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(36))*
 (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(36))) AS DF FROM mysql.T_BASE_KHJL_DJ_QT WHERE NIAN=NF AND JIDU=JD
 UNION ALL
 SELECT EMID,NF,JD,37,toFloat64(XYKSS) AS WCZ,if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(37))*
 (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(37))) AS DF FROM mysql.T_BASE_KHJL_DJ_QT WHERE NIAN=NF AND JIDU=JD
 UNION ALL
 SELECT EMID,NF,JD,38,toFloat64(DFJE) AS WCZ,if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(38))*
 (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(38))) AS DF FROM mysql.T_BASE_KHJL_DJ_QT WHERE NIAN=NF AND JIDU=JD
 UNION ALL
 SELECT EMID,NF,JD,39,toFloat64(SJYHXZ) AS WCZ,if(WCZ<0,0,WCZ)/dictGetFloat64('pas_olap_nj.DICT_SH_CONF_KHJL_ZB','ZSJS',toUInt64(39))*
 (dictGetFloat64('pas_olap_nj.DICT_SH_KHJL_ZBZS','ZBFZ',toUInt64(39))) AS DF FROM mysql.T_BASE_KHJL_DJ_QT WHERE NIAN=NF AND JIDU=JD`
	err = base.CHDB.Exec(sqlStr, dateStr).Error
	if err != nil {
		return
	}
	err = DeleteChTmpTable()
	return
}

func DeleteChTmpTable() (err error) {
	sqlStr := "select name from system.tables where name like 'TMP_YWBB_LS_KHJLDJ_%'"
	rows, err := base.CHDB.Raw(sqlStr).Rows()
	if err != nil {
		return
	}
	defer rows.Close()
	tNames := make([]string, 0, 60)
	for rows.Next() {
		var tName string
		rows.Scan(&tName)
		tNames = append(tNames, tName)
	}
	for _, tName := range tNames {
		err = base.CHDB.Exec(fmt.Sprintf("drop table %s", tName)).Error
		if err != nil {
			return
		}
	}
	return
}

func init() {
	base.DaoPoolObj.Put("KhjlDjQt", base.NewDefaultDao(KhjlDjQtEF(), KhjlDjQtESF(), nil, "my"))
	base.SetAfterCallBack("KhjlDjQt", KhjlDjQtAfterImport)
}
